package com.glomozda.machinerepairdao.repository.order;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.glomozda.machinerepairdao.domain.order.Order;

import static com.glomozda.machinerepairdao.repository.jdbc.DAOJDBCUtil.*;

@SuppressWarnings({"PMD.CommentRequired", "PMD.LawOfDemeter"})
@Repository("orderRepository")
public class OrderRepositoryJDBC implements OrderRepository {
	
	private final transient DataSource ds;

    @Autowired    
    public OrderRepositoryJDBC(final DataSource ds) {
        this.ds = ds;
    }
    
    @Override
    public Order find(final Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Order order = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Orders where orders_id=?");
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	order = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return order;
    }

    @Override
    public List<Order> findAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        final List<Order> orders = new ArrayList<Order>();

        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select * from Orders");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
            	orders.add(map(resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return orders;
    }

    @Override
    public boolean create(final Order order) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int ordersQuantity = 0;
        
        try {
            connection = ds.getConnection();
            preparedStatement = connection.prepareStatement("select count(*) from Orders");
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
            	ordersQuantity = resultSet.getInt(1);
            }
            close(preparedStatement);
            
            preparedStatement = connection.prepareStatement("INSERT INTO Orders "
                    + "(orders_id, client_id, repair_type_id, machine_id, start, status) VALUES (?, ?, ?, ?, ?, ?)");
            preparedStatement.setInt(1, ordersQuantity + 1);
            preparedStatement.setInt(2, order.getClientID());
            preparedStatement.setInt(3, order.getRepairTypeID());
            preparedStatement.setInt(4, order.getMachineID());
            preparedStatement.setDate(5, order.getStart());
            preparedStatement.setString(6, order.getStatus());
            preparedStatement.execute();
            return true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            close(preparedStatement);
            close(connection);
        }
        return false;
    }

    private Order map(final ResultSet resultSet) throws SQLException {
    	final Order order = new Order();
    	order.setOrderID(resultSet.getInt(1));
    	order.setClientID(resultSet.getInt(2));
    	order.setRepairTypeID(resultSet.getInt(3));
    	order.setMachineID(resultSet.getInt(4));
    	order.setStart(resultSet.getDate(5));
    	order.setStatus(resultSet.getString(6));
        return order;
    }

    @Override
    public Order findByStatus(final String status) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Order order = null;

        try {
            connection = ds.getConnection();
            preparedStatement = connection.
                    prepareStatement("select * from Orders where status = ?");
            preparedStatement.setString(1, status);
            resultSet = preparedStatement.executeQuery();
                      
            if (resultSet.next()) {
            	order = map(resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(resultSet);
            close(preparedStatement);
            close(connection);
        }

        return order;
    }
}
